﻿CREATE procedure spAllocateInventory( @OrderID int, @AllowPartialShipments bit )
as
begin
	set nocount on

	declare @CreditAccountID int
	declare @DebitAccountID int
	declare @ProductID int
	declare @Autobuild bit
	declare @Qty int
	declare @AllocateBackorders bit
	declare @ErrorMessage nvarchar(500)
 
	select @CreditAccountID = T3.InventoryAccountID, @DebitAccountID = T3.CostOfGoodsSoldAccountID
	from tblOrderHeader T1
		join tblCustomer T2 on T1.CustomerID = T2.CustomerID
		join tblOwner T3 on T2.OwnerID = T3.OwnerID
	where T1.OrderID = @OrderID

	-- Check to make sure there is no inventory allocated to this order that is moving through processing
	if exists ( select 1 from tblInventoryTransaction T1
		join tblInventory T2 on T1.InventoryID = T2.InventoryID
		join tblOrderDetail T3 on T1.OrderID = T3.OrderID and T2.ProductID = T3.ProductID
		where T1.OrderID = @OrderID
			and T1.InventoryTransactionStatusCode not in ( 'X', 'A', 'B', 'L' ) )
	begin
		RAISERROR( 'Inventory allocation cannot be changed after inventory has been picked for this order', 18, -1 )
	end

	-- If the correct inventory is already allocated, then return
	declare @alreadyAllocated table ( ProductID int, Qty int )
	insert into @alreadyAllocated ( ProductID, Qty )
	select T2.ProductID, T1.Qty
	from tblInventoryTransaction T1
		join tblInventory T2 on T1.InventoryID = T2.InventoryID
	where T1.OrderID = @OrderID
		and T1.InventoryTransactionStatusCode in ( 'A' )

	declare checkAllocation cursor local for select T1.Qty, T1.ProductID
		from tblOrderDetail T1
			join tblProduct T2 on T1.ProductID = T2.ProductiD
		where T2.InventoryItem = 1
			and T1.OrderID = @OrderID
	open checkAllocation
	fetch next from checkAllocation into @Qty, @ProductID
	while @@FETCH_STATUS = 0
	begin
		update @alreadyAllocated set Qty = Qty + case when @Qty <= (0 - Qty) then @Qty else (0 - Qty) end,
			@Qty = @Qty - case when @Qty <= (0 - Qty) then @Qty else (0 - Qty) end
		where ProductID = @ProductID

		if @Qty > 0 and exists (
			select 1
			from dbo.ProductBuildPlan(@ProductID) as T1
				left outer join @alreadyAllocated T2 on T1.ProductID = T2.ProductID
			where coalesce(0 - T2.Qty, 0) < @Qty * T1.Qty )
			goto continue_allocation

		update T2 set Qty = T2.Qty + (@Qty * T1.Qty)
		from dbo.ProductBuildPlan(@ProductID) as T1
			join @alreadyAllocated T2 on T1.ProductID = T2.ProductID

		fetch next from checkAllocation into @Qty, @ProductID
	end
	close checkAllocation
	deallocate checkAllocation
	if not exists ( select 1 from @alreadyAllocated where Qty <> 0 )
	begin
		return
	end

continue_allocation:
	-- Cancel any existing allocations
	update T1 set InventoryTransactionStatusCode = 'X'
	from tblInventoryTransaction T1
	where T1.OrderID = @OrderID
		and InventoryTransactionStatusCode not in ( 'X', 'L', 'C' )

	-- Get the quantities from the order that need to be allocated
	declare @products table ( qty int, ProductID int, Autobuild bit )
	insert into @products ( qty, ProductID, Autobuild )
	select T1.Qty, T1.ProductID, T2.Autobuild
	from tblOrderDetail T1
		join tblProduct T2 on T1.ProductID = T2.ProductID
	where T1.OrderID = @OrderID
		and T2.InventoryItem = 1

	set @AllocateBackorders = 0

	while (select sum(qty) from @products) > 0
	begin
		declare @LocationID int
		set @LocationID = null

		-- Look for locations that have the most available inventory
		-- to allocate to this order
		select top 1 @LocationID = T2.LocationID
		from @products T1
			, tblLocation T2
		where T1.Qty > 0
			and T2.LocationTypeCode = 'W'
			and dbo.AvailableInventory(T1.ProductID, T2.LocationID) > 0
		group by T2.LocationID
		order by sum( case 
			when dbo.AvailableInventory(T1.ProductID, T2.LocationID) >= T1.Qty then T1.Qty 
			else dbo.AvailableInventory(T1.ProductID, T2.LocationID) end ) desc

		-- Allocate backorders from the closest location
		if @LocationID is null
		begin
			set @LocationID = 1
			set @AllocateBackorders = 1
		end

		declare cProducts cursor local for select ProductID, Qty, Autobuild
			from @products
			where qty > 0
		open cProducts
		fetch next from cProducts into @ProductID, @Qty, @Autobuild
		while @@fetch_status = 0
		begin
			-- If nothing about this productID is inventoried then skip it.
			if not exists ( select 1 from dbo.ProductBuildPlan(@ProductID) T1
				join tblProduct T2 on T1.ProductID = T2.ProductID
				where T2.InventoryItem = 1)
			begin
				update @products set Qty = 0 where ProductID = @ProductID
				continue
			end

			declare @QtyToAllocate int
			if @AllocateBackorders = 1
				set @QtyToAllocate = @Qty
			else
				set @QtyToAllocate = case when dbo.AvailableInventory(@ProductID, @LocationID) < @Qty then dbo.AvailableInventory(@ProductID, @LocationID) else @Qty end

			update @products set qty = qty - @QtyToAllocate
			where ProductID = @ProductID

			declare @Qty1 int
			set @Qty1 = 0

			-- If autobuild is set then we will allocate inventory from components later
			-- otherwise we backorder the product not the components
			if @Autobuild = 1
				select @Qty1 = case when sum(Qty) > @QtyToAllocate then @QtyToAllocate else sum(Qty) end
				from tblInventory
				where ProductID = @ProductID
					and LocationID = @LocationID
			else
				set @Qty1 = @QtyToAllocate

			if @Qty1 > 0
				exec spAllocateFromLocation @OrderID, @CreditAccountID, @DebitAccountID, @LocationID, @Qty1, @ProductID

			set @QtyToAllocate = @QtyToAllocate - @Qty1

			-- Allocate the components
			if @Autobuild = 1 and @QtyToAllocate > 0
			begin
				declare cAllocateComponents cursor local for
					select T1.ProductID, @QtyToAllocate * Qty
					from dbo.ProductBuildPlan(@ProductID) T1
						join tblProduct T2 on T1.ProductID = T2.ProductID
					where T2.InventoryItem = 1
				declare @ProductID1 int
				open cAllocateComponents
				fetch next from cAllocateComponents into @ProductID1, @Qty1
				while @@fetch_status = 0
				begin
					exec spAllocateFromLocation @OrderID, @CreditAccountID, @DebitAccountID, @LocationID, @Qty1, @ProductID1
					fetch next from cAllocateComponents into @ProductID1, @Qty1
				end
				close cAllocateComponents
				deallocate cAllocateComponents
			end

			fetch next from cProducts into @ProductID, @Qty, @Autobuild
		end
		close cProducts
		deallocate cProducts
	end

	-- If there are backorder items then set the order status to Backorder
	if exists ( select 1 from tblInventoryTransaction where OrderID = @OrderID and InventoryTransactionStatusCode = 'B' )
		and ( select OrderStatusCode from tblOrderHeader where OrderID = @OrderID ) <> 'B'
		update tblOrderHeader set OrderStatusCode = 'B' where OrderID = @OrderID

	-- If there are no backorded items then set the order status to Pending
	if not exists ( select 1 from tblInventoryTransaction where OrderID = @OrderID and InventoryTransactionStatusCode = 'B' )
		and ( select OrderStatusCode from tblOrderHeader where OrderID = @OrderID ) = 'B'
		update tblOrderHeader set OrderStatusCode = case when coalesce(PrePaid, 0) = 1 then 'E' else 'P' end where OrderID = @OrderID
end